from sqlalchemy import Column

from geosoup import SqlSoup,Session,GeoJsonEncoder

from sqlalchemy import * 
from sqlalchemy.orm import *
from sqlalchemy.sql import expression
import re
import string
from pyparsing import Literal, CaselessLiteral, Word, Upcase, delimitedList, Optional, \
    Combine, Group, alphas, nums, alphanums, ParseException, Forward, oneOf, quotedString, \
    ZeroOrMore, restOfLine, Keyword, commaSeparatedList, CharsNotIn, CaselessKeyword, QuotedString, alphas8bit, \
    NotAny, ParserElement, Regex

binop_map = {
  'eq': '==',
  'ne': '!=',
  'gt': '>',
  'ge': '>=',
  'lt': '<',
  'le': '<='
}

E = CaselessLiteral("E")
DEFAULT,NULL,TRUE,FALSE = map(CaselessKeyword, "default null true false".split())
keywords = DEFAULT | NULL | TRUE | FALSE
binop = oneOf("eq ne lt le gt ge add sub mul div mod", caseless=True)
ident = Word(alphanums + alphas8bit + "_")
name = Optional('"') + delimitedList( ident, ".", combine=True ) + Optional('"')
columns = Group(delimitedList( name ))
exp_join = oneOf("and or")
arithSign = Word("+-",exact=1)
realNum = Regex(r"[+-]?(\d+\.\d*|\.\d+)([Ee][+-]?\d+)?")
intNum = Regex(r"[+-]?\d+([Ee][+-]?\d+)?")
value = realNum | intNum | quotedString | name | keywords
expression = Optional('(') + name + binop + value + Optional(')')
whereExpression = expression + ZeroOrMore(exp_join + expression)
whereExpression.setResultsName( "where" )

columns = []
def parse(f,q):
  ce = expression._clause_element_as_expr(q)
  try:
    columns = ce._raw_columns
  except:
    columns = ce.columns._data.values()
  fe = []
  tokens = whereExpression.parseString(f)
  for t in tokens:
    pass
  print tokens

if __name__ == "__main__":
  TABLE_PREFIX = 'pdx_'
  engine = create_engine('sqlite:///test.sqlite')
  meta = MetaData(engine)
  meta.reflect(bind=engine)
  for table in reversed(meta.sorted_tables):
      if table.name.startswith(TABLE_PREFIX):
        if not table.primary_key.columns:
          table = Table(table.name, meta, Column('ROWID', Integer, primary_key=True),useexisting=True)
  
  soup = SqlSoup(meta)
  session = soup.session
  table1 = soup.entity('pdx_zip_codes')
  table2 = soup.entity('pdx_traffic_signals')
  q = table1.join((table2,table2.ROWID == table1.ROWID))
  q = table1
  f = "(table.NAME Eq 'PORTLAND') and STATE eq 'OREGON'"
  parse(f,q)
